package com.guldan.thrall.util;


import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import com.guldan.core.enums.ValidatingBankReturnTypeEn;
import com.guldan.core.exception.BusinessException;
import com.guldan.core.utils.DateUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSON;
import com.guldan.jaina.dto.register.summary.SummaryDetailDTO;
import com.guldan.jaina.dto.register.summary.SummaryUploadDTO;

public class RegisterSummaryExcel {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
    public static final String PROCESSING = "Processing...";

    /**
     * read the Excel file
     *
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
    public SummaryUploadDTO readExcel(String path, String summaryId) throws IOException {
        if (path == null || EMPTY.equals(path)) {
            return null;
        } else {
            String postfix = getPostfix(path);
            if (!EMPTY.equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path, summaryId);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(path);
                }
            } else {
                System.out.println(path + NOT_EXCEL_FILE);
            }
        }
        return null;
    }


    /**
     * Read the Excel 2010
     *
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    public SummaryUploadDTO readXlsx(String path) throws IOException {
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        SummaryUploadDTO dto = null;
        List<SummaryDetailDTO> list = new ArrayList<SummaryDetailDTO>();
        dto.setList(list);
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }

            System.out.println("xssfSheet.getPhysicalNumberOfRows():" + xssfSheet.getPhysicalNumberOfRows());
            // Read the Row
            for (int rowNum = 3; rowNum <= xssfSheet.getPhysicalNumberOfRows(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    SummaryDetailDTO dataDto = new SummaryDetailDTO();
                    String identNumber = xssfRow.getCell(5).getStringCellValue();
                    String bankPayDate = xssfRow.getCell(7).getStringCellValue();
                    String pass = xssfRow.getCell(8).getStringCellValue();
                    String failReason = xssfRow.getCell(9) == null ? "" : xssfRow.getCell(9).getStringCellValue();
                    dataDto.setIdentNumber(identNumber);
                    dataDto.setBankPayDate(bankPayDate);
                    if (pass.equals("1"))
                        dataDto.setIsSucceed(true);
                    else
                        dataDto.setIsSucceed(false);
                    dataDto.setFailReason(failReason);
                    list.add(dataDto);
                }
            }
        }
        return dto;
    }

    /**
     * Read the Excel 2003-2007
     *
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    //public List<RegisterTemplateDataDTO> readXls(String path) throws IOException {
    public SummaryUploadDTO readXls(String path, String summaryId) throws IOException, BusinessException {
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        SummaryUploadDTO dto = new SummaryUploadDTO();
        List<SummaryDetailDTO> list = new ArrayList<SummaryDetailDTO>();
        dto.setList(list);
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {

            if (numSheet == 1) {
                //只读取第一个表的信息
                break;
            }

            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }

            int a = hssfSheet.getPhysicalNumberOfRows();
            if (hssfSheet.getPhysicalNumberOfRows() < 3) {
                throw new BusinessException("未找到，人员记录");
            }

            //读取汇总批次id
            HSSFRow hr = hssfSheet.getRow(0);
            String summaryIdExl = getStringCellValue(hr.getCell(1));
            if (!summaryId.equals(summaryIdExl)) {
                throw new BusinessException("选择文件批次id不正确，请核实！");
            }

            // Read the Row
            for (int rowNum = 2; rowNum <= hssfSheet.getPhysicalNumberOfRows(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {

                    if (isEndRow(hssfRow)) {
                        break;
                    }

                    SummaryDetailDTO dataDto = new SummaryDetailDTO();
                    DecimalFormat format = new DecimalFormat("#");

                    //取，身份证号码
                    String identNumber = getStringCellValue(hssfRow.getCell(3));

                    if (StringUtils.isBlank(identNumber)) {
                        throw new BusinessException(String.format(ValidatingBankReturnTypeEn.infoCanNotNull.getMean(), rowNum - 1, "身份证号码"), ValidatingBankReturnTypeEn.infoCanNotNull.getCode().toString());
                    }
                    //取，入账日期
                    String bankPayDate = getStringCellValue(hssfRow.getCell(7));
                    if (StringUtils.isBlank(bankPayDate)) {
                        throw new BusinessException(String.format(ValidatingBankReturnTypeEn.infoCanNotNull.getMean(), rowNum - 1, "入账日期"), ValidatingBankReturnTypeEn.infoCanNotNull.getCode().toString());
                    } else {
                        if (!DateUtil.checkFormat(bankPayDate, DateUtil.DAY_FORMAT)) {
                            throw new BusinessException(String.format(ValidatingBankReturnTypeEn.bankDateFormatError.getMean(), rowNum - 1), ValidatingBankReturnTypeEn.bankDateFormatError.getCode().toString());
                        }
                    }

                    //取，入账结果
                    String pass = getStringCellValue(hssfRow.getCell(8));
                    if (StringUtils.isBlank(pass)) {
                        throw new BusinessException(String.format(ValidatingBankReturnTypeEn.infoCanNotNull.getMean(), rowNum - 1, "入账结果"), ValidatingBankReturnTypeEn.infoCanNotNull.getCode().toString());
                    } else {
                        if (!pass.equals("1") && !pass.equals("2")) {
                            throw new BusinessException(String.format(ValidatingBankReturnTypeEn.formatErrorByRow.getMean(), rowNum - 1, "入账结果"), ValidatingBankReturnTypeEn.formatErrorByRow.getCode().toString());
                        }
                    }

                    //取，失败原因
                    String failReason = getStringCellValue(hssfRow.getCell(9));
                    failReason = failReason == null ? "" : failReason;

                    dataDto.setIdentNumber(identNumber);
                    dataDto.setBankPayDate(bankPayDate);
                    if (pass.equals("1"))
                        dataDto.setIsSucceed(true);
                    else
                        dataDto.setIsSucceed(false);
                    dataDto.setFailReason(failReason);

                    list.add(dataDto);
                }
            }
        }
        return dto;
    }

    private String getStringCellValue(HSSFCell cell) {
        if (cell != null) {
            cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
            return cell.getStringCellValue() != null ? cell.getStringCellValue() : "";
        }
        return "";
    }

    @SuppressWarnings("static-access")
    private String getValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    private Boolean isEndRow(HSSFRow hssfRow) {
        boolean ret = true;
        int total = hssfRow.getPhysicalNumberOfCells();
        for (int i = 0; i < total; i++) {
            if (StringUtils.isNotBlank(getStringCellValue(hssfRow.getCell(i)))) {
                ret = false;
                break;
            }
        }
        return ret;
    }

    public static void main(String[] args) {
        RegisterSummaryExcel excel = new RegisterSummaryExcel();
        String filePath = "C:\\Users\\Silas\\Desktop\\测试数据\\20170119000000-IJEMou.xls";
        try {
            SummaryUploadDTO dto = excel.readExcel(filePath,"");
            System.out.println(dto);

            List<SummaryDetailDTO> list = dto.getList();
            if (list != null)
                System.out.println(JSON.toJSONString(list));
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return EMPTY;
    }
}